package com.example.anju.dao;

import com.example.anju.entity.Person;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author CoderXshuai
 * @date 2021/7/5/0005 15:29
 */
public class DatabaseDao {
    @Resource    // 自动注入，spring boot会帮我们实例化一个对象
    private JdbcTemplate jdbcTemplate;

    List<Person> showAll() {
        String sql = "select personid, personname, areainfo.areaname, personage, personaddress\n" +
                "        from personinfo,\n" +
                "             areainfo\n" +
                "        where personinfo.personareaid = areainfo.areaid";
        List<Person> personList = jdbcTemplate.query(sql, new RowMapper<Person>() {
            @Override
            public Person mapRow(ResultSet resultSet, int i) throws SQLException {
                Person p = new Person();
                p.setPersonId((Integer) resultSet.getObject("personid"));
                p.setPersonName((String) resultSet.getObject("personname"));
                p.setPersonArea((String) resultSet.getObject("areaname"));
                p.setPersonAge((Integer) resultSet.getObject("personage"));
                p.setPersonAddress((String) resultSet.getObject("personaddress"));
                return p;
            }
        });
        return personList;
    }

    /**
     * 更新
     *
     * @param newPerson 具体人员信息
     * @return 影响
     */
    int updatePerson(Person newPerson) {
        String sql = "UPDATE personinfo SET personname=? ,personage=? ,personaddress=? WHERE personareaid=(SELECT areaid FROM areainfo WHERE areaname=?)";
        return jdbcTemplate.update(sql, newPerson.getPersonName(), newPerson.getPersonAge(), newPerson.getPersonAddress(), newPerson.getPersonArea());
    }
}
